package service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import base.*;
import pojo.*;
public class OrderService extends BaseService {
	public class OrderRowMapper implements RowMapper<order> {
		
	  	   public order mapRow(ResultSet rs, int rownum) throws SQLException {
	  
		order inf = new order();
		inf.setId(rs.getInt("id"));
		inf.setTime(rs.getTimestamp("time"));
		inf.setCoad(rs.getString("coad"));
		inf.setTotalNum(rs.getInt("totalnum"));
		inf.setTotalPrice(rs.getFloat("totalprice"));
		inf.setTradeStatus(rs.getInt("tradeStatus"));
		inf.setPay(rs.getInt("pay"));	
		return inf;
		
}
	  	   //计算订单总数
	} 
	public int getALL(String where){
		String sql = "SELECT count(*) FROM `order` "+where+"";
		
		int count1 = this.jdbcTemplate.queryForInt(sql);
		
		return count1;
		
	}
	//订单列表
	public List<order> getOrder(int pageno,int pagesize,String where){
		String sql = "select id,time,coad,totalNum,totalPrice,tradeStatus,pay from `order` "+where+" ORDER BY `order`.time limit "+(pageno-1)*pagesize+","+pagesize;
		return jdbcTemplate.query(sql,new OrderRowMapper());
}
	//根据订单号获取订单详细信息
    public order getOrderDetail(String coad){
    	@SuppressWarnings("unused")
		class RowMapper implements org.springframework.jdbc.core.RowMapper<order> {
			 public order mapRow(ResultSet rs, int rownum) throws SQLException {
				 order or = new order();
				    or.setMemberId(rs.getInt("memberId"));
					or.setCustomerName(rs.getString("customerName"));
					or.setCheckinTime(rs.getTimestamp("checkinTime"));
					or.setType(rs.getInt("type"));
					or.setTableArea(rs.getInt("tableArea"));
					or.setTableType(rs.getInt("tableType"));
					or.setPhone(rs.getString("phone"));
					or.setIDcard(rs.getString("IDcard"));
					or.setPay(rs.getInt("pay"));				
					return or;
							}
					}	
		
		String sql="select  memberId,customerName,checkinTime,type,tableArea,tableType,phone,IDcard,pay from `order` where coad=?";
		order or = null;
		
		try{
			or= jdbcTemplate.queryForObject(sql, new RowMapper(),coad);
		
		}catch(EmptyResultDataAccessException e){
		
			}
		
		return or;
	}
    
    //订单菜品列表
    public List<orderDetail> getorderDishes(String coad){
    	class RowMapper implements org.springframework.jdbc.core.RowMapper<orderDetail> {
			 public orderDetail mapRow(ResultSet rs, int rownum) throws SQLException {

				    orderDetail od = new orderDetail();
					od.setDishesName(rs.getString("dishesName"));
					od.setNumber(rs.getInt("number"));
					od.setPrice(rs.getInt("price"));				
					return od;
							}
					}		
    	
    	String sql="select dishesname,number,price from `orderdetail` where orderId in (select id from `order` where coad=?)";
		return jdbcTemplate.query(sql,new RowMapper(),coad);
    	
    }
    //更改订单状态
    public int updateOrderStatus(int id,int orderStatus ){
		
		String sql ="update `order` set tradeStatus ="+orderStatus+" where id=?";
		
		return jdbcTemplate.update(sql,id);
		
 }	
//删除订单
    public int deleteOrder(int id){
    	String sql="delete from `order` where id=?";
    	return jdbcTemplate.update(sql,id);
    	
		
    }
//删除订单详情
	public void deleteOrderDetail(int id){
		String sql="delete from `orderdetail` where orderid="+id;
		jdbcTemplate.update(sql);
	}
    	//根据订单号获取订单信息
    public order getOrderDetail(int id){
    	@SuppressWarnings("unused")
		class RowMapper implements org.springframework.jdbc.core.RowMapper<order> {
			 public order mapRow(ResultSet rs, int rownum) throws SQLException {
				 order or = new order();
				    or.setCoad(rs.getString("coad"));
				    or.setMemberId(rs.getInt("memberId"));
					or.setCustomerName(rs.getString("customerName"));
					or.setCheckinTime(rs.getTimestamp("checkinTime"));
					or.setType(rs.getInt("type"));
					or.setTableArea(rs.getInt("tableArea"));
					or.setTableType(rs.getInt("tableType"));
					or.setPhone(rs.getString("phone"));
					or.setIDcard(rs.getString("IDcard"));
					or.setTotalNum(rs.getInt("totalNum"));
					or.setTotalPrice(rs.getFloat("totalPrice"));
					or.setPay(rs.getInt("pay"));				
					return or;
							}
					}	
		
		String sql="select coad, memberId,customerName,checkinTime,type,tableArea,tableType,totalNum,totalPrice,phone,IDcard,pay from `order` where id=?";
		order or = null;
		
		try{
			or= jdbcTemplate.queryForObject(sql, new RowMapper(),id);
		
		}catch(EmptyResultDataAccessException e){
		
			}
		
		return or;
	}
    //订单流水号
    public  String getOrderNo(int userId){

        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd HH:mm:ss");
        String nowdate = sdf.format(new Date());
        String year=nowdate.substring(0,8);
        String hour=nowdate.substring(9,11);
        String min=nowdate.substring(12,14);
        String sec=nowdate.substring(15,17);

        return year+hour+min+sec+userId;
    }
//提交购物车及订单
    public int postOrder(final order postOrder){
		 PreparedStatementCreator psc = new PreparedStatementCreator() {
			String sql ="insert into `order`(time,coad,memberId,customerName,checkinTime,type,tableArea,tableType,totalNum,totalPrice,phone,IDcard) values(?,?,?,?,?,?,?,?,?,?,?,?)";
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps =con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
				ps.setTimestamp(1, postOrder.getTime());
				ps.setString(2, postOrder.getCoad());
				ps.setInt(3, postOrder.getMemberId());
				ps.setString(4, postOrder.getCustomerName());
				ps.setTimestamp(5, postOrder.getCheckinTime());
				ps.setInt(6, postOrder.getType());
				ps.setInt(7, postOrder.getTableArea());
				ps.setInt(8, postOrder.getTableType());
				ps.setInt(9, postOrder.getTotalNum());
				ps.setFloat(10, postOrder.getTotalPrice());
				ps.setString(11, postOrder.getPhone());
				ps.setString(12, postOrder.getIDcard());
				return ps;
			}

		};
		KeyHolder key = saveandgetkey(psc);
		int a=key.getKey().intValue();
		return a;
		 }
	 
	 public void add(orderDetail c,int r){
		 String sql=" insert into `orderdetail` (orderId,dishesName,price,number) values(?,?,?,?)";
		 jdbcTemplate.update(sql,r,c.getDishesName(),c.getPrice(),c.getNumber());
		 //
	 }
	 
	 public KeyHolder saveandgetkey(PreparedStatementCreator psc){
		 KeyHolder keyHolder =new  GeneratedKeyHolder();
		 jdbcTemplate.update(psc, keyHolder); 
		    return keyHolder;
		 
		     
	 }
	 
     //不同状态下的订单数
        public int countNum(int num){
        	String sql="select count(*) from `order` where tradeStatus="+num;
            return this.jdbcTemplate.queryForInt(sql);
        }
        //查询当天来就餐的订单数
    public int todayOrder(){
    	Timestamp date=new Timestamp(System.currentTimeMillis());
    	SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
    	String a = df.format(date);
    	
    	String sql="select count(*) from `order` where checkinTime like '"+a+"%' and tradeStatus=3";
    	return this.jdbcTemplate.queryForInt(sql);
    	
    }
    //付款改变订单状态
 public void pay(int id ){
		
		String sql ="update `order` set tradeStatus = 3  where id="+id;
		
		jdbcTemplate.update(sql);
		
 }	//申请退款
 public void update(int id , int orderstatus ){
		
		String sql ="update `order` set tradeStatus = "+orderstatus+"  where id="+id;
		
		jdbcTemplate.update(sql);
		
}	
 public void pay1(int id,int pay ){
		
		String sql ="update `order` set  pay = "+pay+" where id="+id;
		
		jdbcTemplate.update(sql);
		
}	
}
